/**
 * 方式二： 先把数据全部写入categoryOption，成功后，遍历categoryOption，创建relation表
 */
const fs = require('fs');
const moment = require('moment');
const Excel = require('exceljs');
const path = require('path');
const async = require('async');
const Promise = require('bluebird');
let models;
let newPath = path.join(__dirname, `dist/${moment().format('YYYY-MM-DD')}.db`);

function createDb(files, logger, callback) {

    function copyDb(cb) {
        let rs = fs.createReadStream(path.resolve(__dirname, './asset/blank.db'));
        let ws = fs.createWriteStream(newPath);
        rs.on('error', function (err) {
            cb(err);
        });
        ws.on('error', function (err) {
            cb(err);
        });
        ws.on('finish', function () {
            cb(null, newPath);
        })
        rs.pipe(ws);
    }

    function initDb(path, cb) {
        log(`成功复制blank.db`)
        models = require('./model')(path);
        models.sequelize.sync({
            force: true
        }).then(() => {
            log(`成功初始化db...`);
            cb(null);
        }, err => cb(err));
    }

    function parseProductExcel(cb) {
        let workbook = new Excel.Workbook();
        workbook.xlsx.readFile(files.product)
            .then(() => {
                let worksheet = workbook.getWorksheet('产品大类');
                let types = [];
                worksheet.eachRow(function (row, rowNumber) {
                    if (rowNumber === 1) return;
                    types.push({
                        _id: row.getCell(1).value,
                        desc: row.getCell(2).value,
                        img: row.getCell(3).value
                    });
                });
                return models.Category.bulkCreate(types).then(() => log(`产品类别表导入${types.length}条记录`));
            }).then(() => {
            let worksheet = workbook.getWorksheet('产品详情');
            let products = [];
            worksheet.eachRow(function (row, rowNumber) {
                if (rowNumber === 1) return;
                products.push({
                    name: filterStr(row.getCell(2).value),
                    thumbnail: filterStr(row.getCell(3).value),
                    conditionLive: filterStr(row.getCell(4).value),
                    conditionWork: filterStr(row.getCell(5).value),
                    conditionShop: filterStr(row.getCell(6).value),
                    poster: row.getCell(7).value,
                    desc: filterStr(row.getCell(8).value),
                    detail: filterStr(row.getCell(9).value),
                    bonus: row.getCell(10).value,
                    categoryId: row.getCell(1).value
                });
            });
            return models.Product.bulkCreate(products).then(() => log(`产品详情表导入${products.length}条记录`));
        })
            .then(() => cb(null))
            .catch(err => cb(err));
    }

    function filterStr(str) {
        return unescape(escape(str));
    }

    function importBonusOption(environment, workbook) {
        let data = [];
        for (let v of['位置', '环境', '物业', '门禁', '楼层']) {
            data = data.concat(readBonusSheet(v, environment, workbook.getWorksheet(v)));
        }
        return models.BonusOption.bulkCreate(data).then(() => log(`bonusOption导入${data.length}条记录`));
    }

    function importCategoryOption(environment, workbook) {
        let data = [];
        for (let v of['建面', '户型', '阳台']) {
            data = data.concat(readDeviceNumSheet(v, environment, workbook.getWorksheet(v)));
        }
        return models.CategoryOption.bulkCreate(data).then(() => log(`categoryOption导入${data.length}条记录`));
    }

    function parseConditionExcel(filePath, environment, cb) {
        let workbook = new Excel.Workbook();
        workbook.xlsx.readFile(filePath)
            .then(() => {
                return Promise.all([importBonusOption(environment, workbook), importCategoryOption(environment, workbook)])
            })
            .then(() => cb(null))
            .catch(err => cb(err))
    }

//读取categoryOption,relate添加数据
//如果数据量很大可以考虑分批次插入
    function processNumRelation(cb) {
        models.CategoryOption.findAll().then(categoryOptions => {
            let totalData = [];
            for (let obj of categoryOptions) {
                if (obj.cameraNum) {
                    totalData.push({
                        num: obj.cameraNum,
                        categoryId: 1,
                        optionId: obj._id
                    });
                }
                if (obj.lockNum) {
                    totalData.push({
                        num: obj.lockNum,
                        categoryId: 2,
                        optionId: obj._id
                    });
                }
                if (obj.detectNum) {
                    totalData.push({
                        num: obj.detectNum,
                        categoryId: 3,
                        optionId: obj._id
                    });
                }
                if (obj.warnNum) {
                    totalData.push({
                        num: obj.warnNum,
                        categoryId: 4,
                        optionId: obj._id
                    });
                }
                if (obj.handNum) {
                    totalData.push({
                        num: obj.handNum,
                        categoryId: 5,
                        optionId: obj._id
                    });
                }
            }
            return models.CategoryRelateOption.bulkCreate(totalData).then(() => log(`categoryRelateOption导入${totalData.length}条记录`));
        })
            .then(() => cb(null))
            .catch(err => cb(err))
    }

    //复制一张categoryOption表，不需要多余字段
    function alterCategoryOptionTable(cb) {
        models.sequelize.query('DROP TABLE IF EXISTS categoryOption').then(() => log('categoryOption删除成功'))
            .then(() => {
                return models.sequelize.query('ALTER TABLE categoryOptionSpread rename to categoryOption').then(() => log('重命名categoryOptionSpread成功'));
            })
            .then(() => cb(null)).catch(err => cb(err));
    }

    const conditionExcels = [];
    conditionExcels.push({
        filePath: path.join(files.location),
        environment: '居住'
    });
    conditionExcels.push({
        filePath: path.join(files.office),
        environment: '办公'
    });
    conditionExcels.push({
        filePath: path.join(files.business),
        environment: '经营'
    });

    function run() {
        async.auto({
            task1: copyDb,
            task2: ['task1', (data, cb) => initDb(data.task1, cb)],
            task3: ['task2', (data, cb) => parseProductExcel(cb)],
            task4: ['task2', (data, cb) => {
                async.eachLimit(conditionExcels, 2, (obj, cb) => {
                    parseConditionExcel(obj.filePath, obj.environment, cb);
                }, (err, result) => {
                    if (err) return cb(err);
                    cb(null);
                });
            }],
            task5: ['task3', 'task4', (data, cb) => {
                models.sequelize.query('INSERT INTO categoryOptionSpread(_id,type,environment,desc,img) SELECT _id,type,environment,desc,img FROM categoryOption')
                    .then(() => log('categoryOptionSpread复制成功'))
                    .then(() => cb(null))
                    .catch(err => cb(err))
            }],
            task6: ['task5', (data, cb) => processNumRelation(cb)],
            task7: ['task6', (data, cb) => alterCategoryOptionTable(cb)]
        }, (err, result) => {
            if (err) {
                log(`Fail: ${err.message}`);
                callback(err);
                return;
            }
            log('导表结束...');
            callback(null, newPath);
        });
    }

    fs.stat(path.resolve(__dirname, './dist'), (err) => {
        if (err) {
            fs.mkdir(path.resolve(__dirname, './dist'), (err) => {
                if (err) return callback(err);
                run()
            });
        } else {
            run();
        }
    });


    /**
     * 解析计分的sheet
     * @param  {[type]} type        [description]
     * @param  {[type]} environment [description]
     * @param  {[type]} worksheet   [description]
     * @return {[type]}             [description]
     */
    function readBonusSheet(type, environment, worksheet) {
        let bonusOptions = [];
        worksheet.eachRow((row, rowNumber) => {
            if (rowNumber === 1) return;
            bonusOptions.push({
                type: type,
                environment: environment,
                desc: row.getCell(2).value,
                img: row.getCell(3).value,
                optionBonus: row.getCell(4).value
            });
        });
        return bonusOptions;
    };

    function readDeviceNumSheet(type, environment, worksheet) {
        let data = [];
        worksheet.eachRow((row, rowNumber) => {
            if (rowNumber === 1) return;
            data.push({
                type: type,
                environment: environment,
                desc: row.getCell(2).value,
                img: row.getCell(3).value,
                cameraNum: row.getCell(4).value,
                lockNum: row.getCell(5).value,
                detectNum: row.getCell(6).value,
                warnNum: row.getCell(7).value,
                handNum: row.getCell(8).value
            });
        });
        return data;
    };

    function log(msg) {
        logger(msg);
    }
}

exports.createDb = createDb;
